package com.scalar.forecast.salesForecastEntry.actionCommands;

import com.scalar.forecast.actionCommands.ActionCommand;
import com.scalar.forecast.salesForecastEntry.forms.SalesForecastEntryForm;
import com.scalar.forecast.salesForecastEntry.RowData;
import com.scalar.forecast.salesForecastEntry.SlotData;
import com.scalar.forecast.salesForecastEntry.ForecastData;
import com.scalar.forecast.projectSetup.dao.UserDefinedFieldsDAO;
import com.scalar.forecast.projectSetup.dao.ProjectSetupDAO;
import com.scalar.forecast.projectSetup.forms.UserDefinedFieldsForm;
import com.scalar.forecast.projectSetup.Project;
import com.scalar.forecast.UserDefinedField;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.ScalarException;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;

/**
 * User: Sujan
 */
public class SalesForecastDownloadCommand extends ActionCommand {
    public ActionForward execute(ActionMapping mapping,
                                 ActionForm form,
                                 HttpServletRequest request,
                                 HttpServletResponse response) throws Exception {
        new SalesForecastEntryNewCommand().execute(mapping, form, request, response);
        SalesForecastEntryForm forecastEntryForm    = ( SalesForecastEntryForm ) form;

        ArrayList slotNames = (ArrayList)request.getAttribute ( "slotNames" );

        Workbook workbook = generateWorkBook(request, forecastEntryForm, slotNames, "Forecast Entry Sheet");
        String fileName = "Forecast Downlaod Report_"+ DateUtil.getTimeStamp() ;
        ExcelUtils.generateExcelReport(workbook, fileName, response );

        return null;
    }
    private Workbook generateWorkBook(HttpServletRequest request, SalesForecastEntryForm forecastEntryForm, ArrayList slotNames, String sheetName) throws ScalarException {
        Workbook workbook = null;
        Sheet sheet = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = ch.createDataFormat();

        sheet = workbook.createSheet(sheetName);
        sheet.setSelected(true);
        ExcelUtils excelUtils = new ExcelUtils(workbook);
        UserDefinedFieldsForm userDefinedFieldsForm = new UserDefinedFieldsForm();
        UserDefinedFieldsDAO.loadUserDefinedFieldNames(null, userDefinedFieldsForm);
        ArrayList<String> headers = new ArrayList<String>();
        headers.add(userDefinedFieldsForm.getList1());
        headers.add("CUSTOMER");
        headers.add("END CUSTOMER");
        headers.add("END USER");
        headers.add("PROJECT NAME");
        headers.add(userDefinedFieldsForm.getText1());
        headers.add("PART NUMBER");
        headers.add("     ");
        headers.addAll(slotNames);

        int nextRowIdx = 0;
        int nextColumnIdx = 0;

        // First Row
        Row row = sheet.createRow(nextRowIdx++);
        Cell cell = excelUtils.mergeCells(sheet, 0, 0, 0, headers.size()-1, excelUtils.CELL_STYLE_TITLE);
        excelUtils.setCellValue(cell, ch.createRichTextString("Forecast Entry Sheet"), excelUtils.CELL_STYLE_TITLE);

        // Header Row
        row = sheet.createRow(nextRowIdx++);
        nextColumnIdx = 0;
        for(int i=0; i < headers.size(); i++) 
        {
            String headerValue = headers.get(i);
            excelUtils.setCellValue(row, nextColumnIdx, ch.createRichTextString(headerValue), excelUtils.CELL_STYLE_HEADER);
            int width = (headerValue.length() > 12) ? headerValue.length() : 12;
            sheet.setColumnWidth(nextColumnIdx++, (((width + 2) * 256)));
        }
        
        List rowDataList = forecastEntryForm.getRowDataList();
        renderRowDataList(request, excelUtils, sheet, nextRowIdx, rowDataList, ch );

        List forecastDataList = forecastEntryForm.getForecastDataList();
        for(Object forecastDataObj: forecastDataList) 
        {
            ForecastData forecastData = (ForecastData) forecastDataObj;
            rowDataList = forecastData.getRowDataList();
            renderRowDataList(request, excelUtils, sheet, nextRowIdx, rowDataList, ch);
            nextRowIdx = nextRowIdx + (rowDataList.size()*3);
        }

        return workbook;
    }
    private void renderRowDataList(HttpServletRequest request, ExcelUtils excelUtils, Sheet sheet, int nextRowIdx, List rowDataList, CreationHelper ch) throws ScalarException
    {
        for(Object rowDataObj: rowDataList) 
        {
            int nextColumnIdx = 0;
            RowData rowData = (RowData)rowDataObj;
            Row row1 = sheet.createRow(nextRowIdx++);
            Row row2 = sheet.createRow(nextRowIdx++);
            Row row3 = sheet.createRow(nextRowIdx++);
            Project project = getProject(request, rowData.getProjId());
            excelUtils.setCellValue(row1, nextColumnIdx++, ch.createRichTextString(getUserDefinedValue(request, rowData.getProjId(), "USERLIST1")), excelUtils.CELL_STYLE_TEXT);
			excelUtils.mergeCells(sheet, row1.getRowNum(), nextColumnIdx-1, row3.getRowNum(), nextColumnIdx-1, excelUtils.CELL_STYLE_TEXT);
            excelUtils.setCellValue(row1, nextColumnIdx++, ch.createRichTextString(rowData.getCustomerName()), excelUtils.CELL_STYLE_TEXT);
			excelUtils.mergeCells(sheet, row1.getRowNum(), nextColumnIdx-1, row3.getRowNum(), nextColumnIdx-1, excelUtils.CELL_STYLE_TEXT);
            excelUtils.setCellValue(row1, nextColumnIdx++, ch.createRichTextString(rowData.getEndCustomerName()), excelUtils.CELL_STYLE_TEXT);
			excelUtils.mergeCells(sheet, row1.getRowNum(), nextColumnIdx-1, row3.getRowNum(), nextColumnIdx-1, excelUtils.CELL_STYLE_TEXT);
            excelUtils.setCellValue(row1, nextColumnIdx++, ch.createRichTextString(project.getEndUserName()), excelUtils.CELL_STYLE_TEXT);
			excelUtils.mergeCells(sheet, row1.getRowNum(), nextColumnIdx-1, row3.getRowNum(), nextColumnIdx-1, excelUtils.CELL_STYLE_TEXT);
            excelUtils.setCellValue(row1, nextColumnIdx++, ch.createRichTextString(rowData.getProjName()), excelUtils.CELL_STYLE_TEXT);
			excelUtils.mergeCells(sheet, row1.getRowNum(), nextColumnIdx-1, row3.getRowNum(), nextColumnIdx-1, excelUtils.CELL_STYLE_TEXT);
            excelUtils.setCellValue(row1, nextColumnIdx++, ch.createRichTextString(getUserDefinedValue(request, rowData.getProjId(), "USERTXT1")), excelUtils.CELL_STYLE_TEXT);
			excelUtils.mergeCells(sheet, row1.getRowNum(), nextColumnIdx-1, row3.getRowNum(), nextColumnIdx-1, excelUtils.CELL_STYLE_TEXT);
            Cell cell = excelUtils.mergeCells(sheet, row1.getRowNum(), nextColumnIdx, row3.getRowNum(), nextColumnIdx, excelUtils.CELL_STYLE_TEXT);
            nextColumnIdx++;
            excelUtils.setCellValue(cell, ch.createRichTextString(rowData.getProductName()), excelUtils.CELL_STYLE_TEXT);

            excelUtils.setCellValue(row1, nextColumnIdx, ch.createRichTextString("Qty"), excelUtils.CELL_STYLE_TEXT);
            excelUtils.setCellValue(row2, nextColumnIdx, ch.createRichTextString("ASP"), excelUtils.CELL_STYLE_TEXT);
            excelUtils.setCellValue(row3, nextColumnIdx++, ch.createRichTextString("Fcst Rev"), excelUtils.CELL_STYLE_TEXT);

            List slotDatalist = rowData.getSlotDataList();
            for(Object slotDataObj:slotDatalist) {
                SlotData slotData = (SlotData)slotDataObj;
                excelUtils.setCellValue(row1, nextColumnIdx, slotData.getQty(), excelUtils.CELL_STYLE_NUMBER, Cell.CELL_TYPE_NUMERIC);
                excelUtils.setCellValue(row2, nextColumnIdx, slotData.getPrice(), excelUtils.CELL_STYLE_NUMERIC, Cell.CELL_TYPE_NUMERIC);
                String revFormula = excelUtils.getCellReference(nextColumnIdx, row1.getRowNum()) + "*" + excelUtils.getCellReference(nextColumnIdx, row2.getRowNum());
                excelUtils.setCellFormula(row3, nextColumnIdx++, revFormula, excelUtils.CELL_STYLE_CURRENCY_$);
            }
        }
    }

    private String getUserDefinedValue(HttpServletRequest request, int projId, String fieldName) throws ScalarException {
        Map<String, Object> reqCacheMap = (Map<String, Object>)request.getAttribute("requestCacheMap");
        if(reqCacheMap == null) {
            reqCacheMap = new HashMap<String, Object>();
            request.setAttribute("requestCacheMap", reqCacheMap);
        }
        List userDefinedFieldsList = null;
        if(reqCacheMap.containsKey(projId+"")) {
            userDefinedFieldsList = (List)reqCacheMap.get(projId+"");
        } else {
            userDefinedFieldsList = UserDefinedFieldsDAO.loadUserDefinedFieldValues(null, projId, "PROJSETUP");
            reqCacheMap.put(projId+"", userDefinedFieldsList);
        }

        for(int i=0; i < userDefinedFieldsList.size(); i++){
            UserDefinedField userDefinedField = (UserDefinedField) userDefinedFieldsList.get(i);
            if(fieldName.equalsIgnoreCase(userDefinedField.getFieldName())) {
                return userDefinedField.getValue();
            }
        }
        return "";
    }

    private Project getProject(HttpServletRequest request, int projId) throws ScalarException {
        Map<String, Object> reqCacheMap = (Map<String, Object>)request.getAttribute("requestCacheMap");
        if(reqCacheMap == null) {
            reqCacheMap = new HashMap<String, Object>();
            request.setAttribute("requestCacheMap", reqCacheMap);
        }
        Project project = null;
        String key = "PROJ:"+projId;
        if(reqCacheMap.containsKey(key)) {
            project = (Project)reqCacheMap.get(key);
        } else {
            project = ProjectSetupDAO.getProject(null, projId);
            reqCacheMap.put(key, project);
        }

        return project;
    }

}